﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;

namespace DAO
{
    public class XeDAO
    {
        public static int Them(XeDTO xeDto)
        {
            int result;
            try
            {
                OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
                string strFormat = "Insert into Xe(BienSo, HangXe, LoaiHang, TrongTai, NgayDangKiem, SoKhung, NamSanXuat, HieuXe, SoMay, NgayTiepNhan, MaNVTiepNhan)";
                strFormat += "Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                OleDbCommand cmd = new OleDbCommand(strFormat, cnn);

                cmd.Parameters.Add("@BienSo", OleDbType.VarChar);
                cmd.Parameters.Add("@HangXe", OleDbType.Integer);
                cmd.Parameters.Add("@LoaiHang", OleDbType.Integer);
                cmd.Parameters.Add("@TrongTai", OleDbType.Integer);
                cmd.Parameters.Add("@NgayDK", OleDbType.Date);
                cmd.Parameters.Add("@SoKhung", OleDbType.VarChar);
                cmd.Parameters.Add("@NamSX", OleDbType.Integer);
                cmd.Parameters.Add("@HieuXe", OleDbType.VarWChar);
                cmd.Parameters.Add("@SoMay", OleDbType.VarChar);
                cmd.Parameters.Add("@NgayTiepNhan", OleDbType.Date);
                cmd.Parameters.Add("@MaNVTiepNhan", OleDbType.VarChar);

                cmd.Parameters["@BienSo"].Value = xeDto.BienSo;
                cmd.Parameters["@HangXe"].Value= xeDto.HangXe;
                cmd.Parameters["@LoaiHang"].Value = xeDto.LoaiHang;
                cmd.Parameters["@TrongTai"].Value = xeDto.TrongTai;
                cmd.Parameters["@NgayDK"].Value = xeDto.NgayDangKiem;
                cmd.Parameters["@SoKhung"].Value = xeDto.SoKhung;
                cmd.Parameters["@NamSX"].Value = xeDto.NamSanXuat;
                cmd.Parameters["@HieuXe"].Value = xeDto.HieuXe;
                cmd.Parameters["@SoMay"].Value = xeDto.SoMay;
                cmd.Parameters["@NgayTiepNhan"].Value = xeDto.NgayTiepNhan;
                cmd.Parameters["@MaNVTiepNhan"].Value = xeDto.NhanVienTiepNhan;

                try
                {
                    result = cmd.ExecuteNonQuery();
                    cnn.Close();
                }
                catch (System.Exception ex)
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        cnn.Close();
                    }
                	throw ex;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }

        public static List<XeDTO> LayDanhSach(int param, LoaiTimKiemMotThamSo loai)
        {
            OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
            List<XeDTO> lstXe = new List<XeDTO>();
            string query = null;

            switch (loai)
            {
                case LoaiTimKiemMotThamSo.HX:
                    {
                        query = String.Format("select * from Xe where HangXe = {0}", param);
                    }
                    break;
                case LoaiTimKiemMotThamSo.LH:
                    {
                        query = String.Format("select * from Xe where LoaiHang = {0}", param);
                    }
                    break;
                case LoaiTimKiemMotThamSo.TT:
                    {
                        query = String.Format("select * from Xe where TrongTai = {0}", param);
                    }
                    break;
            }

            OleDbCommand cmd = new OleDbCommand(query, cnn);

            //try
            {
                OleDbDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    XeDTO xeDto = new XeDTO();
                    xeDto.BienSo = rd["BienSo"].ToString();
                    xeDto.HangXe = (int)rd["HangXe"];
                    xeDto.LoaiHang = (int)rd["LoaiHang"];
                    xeDto.TrongTai = (int)rd["TrongTai"];
                    xeDto.NgayDangKiem = (DateTime)rd["NgayDangKiem"];
                    xeDto.SoKhung = rd["SoKhung"].ToString();
                    xeDto.NamSanXuat = (int)rd["NamSanXuat"];
                    xeDto.HieuXe = rd["HieuXe"].ToString();
                    xeDto.SoMay = rd["SoMay"].ToString();
                    xeDto.NgayTiepNhan = (DateTime)rd["NgayTiepNhan"];
                    xeDto.NhanVienTiepNhan = rd["MaNVTiepNhan"].ToString();
                    lstXe.Add(xeDto);
                }
                cnn.Close();
            }
            //catch (Exception ex)
            //{
            //    cnn.Close();
            //    throw ex;
            //}
            return lstXe;

        }

        public static List<XeDTO> LayDanhSach(int param1, int param2, LoaiTimKiemHaiThamSo loai)
        {
            OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
            List<XeDTO> lstXe = new List<XeDTO>();
            string query = null;

            switch(loai)
            {
                case LoaiTimKiemHaiThamSo.HXTT:
                    {
                        query = String.Format("select * from Xe where HangXe = '{0}' and TrongTai = '{1}'", param1, param2);
                    }
                    break;
                case LoaiTimKiemHaiThamSo.LHHX:
                    {
                        query = String.Format("select * from Xe where LoaiHang = '{0}' and HangXe = '{1}'", param1, param2);
                    }
                    break;
                case LoaiTimKiemHaiThamSo.LHTT:
                    {
                        query = String.Format("select * from Xe where LoaiHang = '{0}' and TrongTai = '{1}'", param1, param2);
                    }
                    break;
            }
            OleDbCommand cmd = new OleDbCommand(query, cnn);

            try
            {
                OleDbDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    XeDTO xeDto = new XeDTO();
                    xeDto.BienSo = (string)rd["BienSo"];
                    xeDto.HangXe = (int)rd["HangXe"];
                    xeDto.LoaiHang = (int)rd["LoaiHang"];
                    xeDto.TrongTai = (int)rd["TrongTai"];
                    xeDto.NgayDangKiem = (DateTime)rd["NgayDangKiem"];
                    xeDto.SoKhung = (string)rd["SoKhung"];
                    xeDto.NamSanXuat = (int)rd["NamSanXuat"];
                    xeDto.HieuXe = (string)rd["HieuXe"];
                    xeDto.SoMay = (string)rd["SoMay"];
                    xeDto.NgayTiepNhan = (DateTime)rd["NgayTiepNhan"];
                    xeDto.NhanVienTiepNhan = (string)rd["MaNVTiepNhan"];
                    lstXe.Add(xeDto);
                }
                cnn.Close();
            }
            catch (Exception ex)
            {
                cnn.Close();
                throw ex;
            }

            return lstXe;
        }

        public static List<XeDTO> LayDanhSach(int loaiHang, int hangXe, int trongTai)
        {
            OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
            List<XeDTO> lstXe = new List<XeDTO>();
            string query = null;

            query = String.Format("select * from Xe where LoaiHang = '{0}' and HangXe = '{1}' and TrongTai = '{2}'", loaiHang, hangXe, trongTai);
            
            OleDbCommand cmd = new OleDbCommand(query, cnn);

            try
            {
                OleDbDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    XeDTO xeDto = new XeDTO();
                    xeDto.BienSo = (string)rd["BienSo"];
                    xeDto.HangXe = (int)rd["HangXe"];
                    xeDto.LoaiHang = (int)rd["LoaiHang"];
                    xeDto.TrongTai = (int)rd["TrongTai"];
                    xeDto.NgayDangKiem = (DateTime)rd["NgayDangKiem"];
                    xeDto.SoKhung = (string)rd["SoKhung"];
                    xeDto.NamSanXuat = (int)rd["NamSanXuat"];
                    xeDto.HieuXe = (string)rd["HieuXe"];
                    xeDto.SoMay = (string)rd["SoMay"];
                    xeDto.NgayTiepNhan = (DateTime)rd["NgayTiepNhan"];
                    xeDto.NhanVienTiepNhan = (string)rd["MaNVTiepNhan"];
                    lstXe.Add(xeDto);
                }
                cnn.Close();
            }
            catch (Exception ex)
            {
                cnn.Close();
                throw ex;
            }

            return lstXe;
        }

        public static List<string> LayDanhSach()
        {
            OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
            List<string> lstXe = new List<string>();
            string query = null;

            query = "select * from Xe";

            OleDbCommand cmd = new OleDbCommand(query, cnn);

            try
            {
                OleDbDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    string bienso = (string)rd["BienSo"];
                    
                    lstXe.Add(bienso);
                }
                cnn.Close();
            }
            catch (Exception ex)
            {
                cnn.Close();
                throw ex;
            }

            return lstXe;
        }
    }
}

